package com.boco.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.boco.entity.EMR_MJZSSJL;

public class GetEMR_MJZSSJLData {
	
	private static final Log log = LogFactory.getLog(GetEMR_MJZSSJLData.class);
	public static List<EMR_MJZSSJL> getData()
	{
	    Connection con = null;
	    PreparedStatement pre = null;
	    ResultSet result = null;
	    List<EMR_MJZSSJL> re = new ArrayList<EMR_MJZSSJL>();
	    try
	    {
	        Class.forName("oracle.jdbc.driver.OracleDriver");
	        System.out.println("开始尝试连接数据库！");
	        String url = "jdbc:oracle:" + "thin:@172.20.21.12:1521:orcl";
	        String user = "sjzx_hxk";
	        String password = "boco#1234";
	        con = DriverManager.getConnection(url, user, password);
	        System.out.println("连接成功！");
	        
	        String sql = "select "
	        		+"'' as HZZSYH,"
					+"a.yljgdm as YLJGZZJGDM,"
					+"'' as YLJGMC,"
					+"a.jzlsh as MJZH,"
					+"a.ssid as SSJLLSH,"
					+"c.xm as XM,"
					+"to_char(c.csrq,'yyyy-mm-dd HH24:mi:ss') as CSRQ,"
					+"c.xb as XB,"
					+"c.nls as NL,"
					+"b.ssczbm as SSDM,"
					+"b.ssczmc as SSMC,"
					+"'' as SSSQDH,"
					+"to_char(a.ssrq,'yyyy-mm-dd HH24:mi:ss') as SSRQ,"
					+"a.ssysgh as SSYSGH,"
					+"a.ssysxm as SSYSXM,"
					+"b.ssjb as SSJBDM,"
					+"b.ssjb as SSJBMC,"
					+"b.qkyhdj as QKYHDJDM,"
					+"'' as QKYHDJMC,"
					+"b.mzysgh as MZYSGH,"
					+"b.mzysxm as MZYSMC,"
					+"b.mzfs as MZFFDM,"
					+"b.mzff as MZFFMC,"
					+"'' as SSFLDM,"
					+"b.sslx as SSFLMC,"
					+"to_char(b.sskssj,'yyyy-mm-dd HH24:mi:ss') as SSKSSJ,"
					+"to_char(b.ssjssj,'yyyy-mm-dd HH24:mi:ss') as SSJSSJ,"
					+"'' as SSJG,"
					+"'' as QTYXCZ,"
					+"'' as ZRLSH,"
					+"'' as ORGCODE,"
					+"'' as AREACODE,"
					+"to_char(sysdate,'yyyyMMddHH24miss') as UPLOADTIME,"
					+"'' as  SOURCEID "
					+" from TB_HIS_SSJL a left join TB_HIS_SSMX  b "
					+" on a.yljgdm = b.yljgdm and a.ssid = b.ssid"
					+" left join tb_hzxx  c on b.yljgdm = c.yljgdm"
					+" and b.kh = c.kh and b.klx = c.klx ";
	 
	        log.info("查询sql："+sql);
	        pre = con.prepareStatement(sql);
	        result = pre.executeQuery();
	        System.out.println("执行成功");
	        while (result.next()){
//	            System.out.println(result.getInt("YYDAH"));
	        	EMR_MJZSSJL temp = new EMR_MJZSSJL();
	        
	        	temp.setHZZSYH(result.getString("HZZSYH"));
	        	temp.setYLJGZZJGDM(result.getString("YLJGZZJGDM"));
	        	temp.setYLJGMC(result.getString("YLJGMC"));
	        	temp.setMJZH(result.getString("MJZH"));
	        	temp.setSSJLLSH(result.getString("SSJLLSH"));
	        	
	        	temp.setXM(result.getString("XM"));
	        	temp.setCSRQ(result.getString("CSRQ"));
	        	temp.setXB(result.getString("XB"));
	        	temp.setNL(result.getString("NL"));
	        	temp.setSSDM(result.getString("SSDM"));
	        	temp.setSSMC(result.getString("SSMC"));
	        	temp.setSSSQDH(result.getString("SSSQDH"));
	        	temp.setSSRQ(result.getString("SSRQ"));
	        	temp.setSSYSGH(result.getString("SSYSGH"));
	        	temp.setSSYSXM(result.getString("SSYSXM"));
	        	temp.setSSJBDM(result.getString("SSJBDM"));
	        	temp.setSSJBMC(result.getString("SSJBMC"));
	        	temp.setQKYHDJDM(result.getString("QKYHDJDM"));
	        	temp.setQKYHDJMC(result.getString("QKYHDJMC"));
	        	temp.setMZYSGH(result.getString("MZYSGH"));
	        	temp.setMZYSMC(result.getString("MZYSMC"));
	        	temp.setMZFFDM(result.getString("MZFFDM"));
	        	temp.setMZFFMC(result.getString("MZFFMC"));
	        	temp.setSSFLDM(result.getString("SSFLDM"));
	        	temp.setSSFLMC(result.getString("SSFLMC"));
	        	temp.setSSKSSJ(result.getString("SSKSSJ"));
	        	temp.setSSJSSJ(result.getString("SSJSSJ"));
	        	temp.setSSJG(result.getString("SSJG"));
	        	temp.setQTYXCZ(result.getString("QTYXCZ"));
	        	

	        	temp.setZRLSH(result.getString("ZRLSH"));
	        	temp.setORGCODE(result.getString("ORGCODE"));
	        	temp.setAREACODE(result.getString("AREACODE"));
	        	temp.setUPLOADTIME(result.getString("UPLOADTIME"));
	        	temp.setSOURCEID(result.getString("SOURCEID"));
	        	re.add(temp);
	        }
	    }
	    catch (Exception e)
	    {
	        e.printStackTrace();
	    }
	    finally
	    {
	        try
	        {
	            if (result != null)
	                result.close();
	            if (pre != null)
	                pre.close();
	            if (con != null)
	                con.close();
	            System.out.println("数据库连接已关闭！");
	        }
	        catch (Exception e)
	        {
	            e.printStackTrace();
	        }
	    }
		return re;
	}
}
